package com.buobao.jdbc.demo;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 批处理示例
 * 批处理可以大大缩减循环插入的时间
 */
public class BatchSample {
    public static void main(String[] arg) {
        Connection connection=null;
        PreparedStatement statement = null;

        try {
            long starttime = System.currentTimeMillis();
            connection = DbUtil.getConnection();
            String sql = "insert into employee(eno,ename,salary,dname,hiredate) values (?,?,?,?,?)";
            connection.setAutoCommit(false); //设置事务手动提交
            statement = connection.prepareStatement(sql);
            for (int i=200000;i<400000;i++) {
                statement.setInt(1,i);
                statement.setString(2,"name"+i);
                statement.setFloat(3,i*100.0f);
                statement.setString(4,"研发部");
                statement.setDate(5,new Date(System.currentTimeMillis()));
                statement.addBatch(); //加入批处理
            }
            statement.executeBatch(); //执行批处理
            connection.commit(); //运行到这里数据才会提交
            System.out.println("runtime:"+(System.currentTimeMillis()-starttime));
        } catch (ClassNotFoundException e) {
            try {
                connection.rollback(); //异常则回滚
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(null,statement,connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
}
